package com.gateon.cg.util;


import com.gateon.cg.bean.PropertyAndColumnInfo;
import com.gateon.cg.bean.TableInfo;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * 支持单主键的表，建议主键设置在
 *
 * @author
 */
public class DbUtils {
	private static DbUtils dbUtils = new DbUtils();

	private DbUtils() {

	}

	public static DbUtils getInstance() {
		return dbUtils;
	}

	static Logger logging = LoggerFactory.getLogger(DbUtils.class);

	/**
	 * 返回一个与特定数据库的连接
	 *
	 * @throws ClassNotFoundException
	 */
	public Connection getConnection() throws ClassNotFoundException {
		Connection connection = null;
		try {
			// 加载属性文件，读取数据库连接配置信息
			Properties pro = new Properties();
			try {
				pro.load(DbUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
			} catch (IOException e) {
				System.out.println("未找到配置文件！！！");
			}
			String driverClass = pro.getProperty("jdbc_driverClassName");
			String url = pro.getProperty("jdbc_url");
			String user = pro.getProperty("jdbc_username");
			String password = pro.getProperty("jdbc_password");
			Class.forName(driverClass);
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * 只做单主键代码的生成
	 *
	 * @param metaData
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public String primaryKeyColumnName(DatabaseMetaData metaData, String tableName) throws SQLException {
		String primaryKeyColumnName = null;
		ResultSet primaryKeyResultSet = metaData.getPrimaryKeys(null, null, tableName);
		while (primaryKeyResultSet.next()) {
			primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");
			break;
		}
		if (primaryKeyColumnName == null) {
			primaryKeyColumnName = "id";
		}
		return primaryKeyColumnName;
	}

	/**
	 * 获取需要生成代码的表信息
	 *
	 * @param metaData
	 * @param tableNames
	 * @return
	 * @throws SQLException
	 */
	public List<TableInfo> getAllTables(DatabaseMetaData metaData, List<String> tableNames, boolean underline2Camel)
			throws SQLException {
		List<TableInfo> tables = new ArrayList<TableInfo>();
		ResultSet tableRet = getTableResultSet(metaData);
		while (tableRet.next()) {
			if(tableNames.contains(tableRet.getString("TABLE_NAME").trim())){ // tableNames 是否存在 循环的当前表
				//单表的所有信息
				TableInfo tableInfo = new TableInfo();
				String tableName = tableRet.getString("TABLE_NAME");// 表名
				String tableDesc = tableRet.getString("REMARKS");// 表注释
				if (StringUtils.isBlank(tableDesc)) {
					tableDesc = CommonUtil.setTableCommentByTableName(tableName);  // 设置表注释
				}
				logging.info("===tableName:" + tableName + "-tableDesc:" + tableDesc);
				//字段处理 新
				List<PropertyAndColumnInfo> propertysAndColumnInfos = getAllPropertysAndColumnInfos(metaData, tableName,underline2Camel);



				Set<String> packages = new HashSet<String>();
				// 主键处理(主键唯一)
				String primaryKey = primaryKeyColumnName(metaData, tableName);
				String primaryKeyProperty = primaryKey;
				if (underline2Camel) {
					primaryKeyProperty = Underline2CamelUtils.underline2Camel2(primaryKey);
				}
				Map<String, String> primaryKeyMap = new HashMap<String, String>();
				primaryKeyMap.put(primaryKey, primaryKeyProperty);

				// beanClass
				String beanName = getClassName(tableName, underline2Camel);
				tableInfo.setTableName(tableName);
				tableInfo.setTableDesc(tableDesc);
				tableInfo.setBeanName(beanName);
				tableInfo.setPrimaryKey(primaryKeyMap);
				tableInfo.setPackages(packages);
				tableInfo.setPropertysAndColumnInfos(propertysAndColumnInfos); //新的包含所有信息
				tables.add(tableInfo);

			}
		}

		return tables;
	}

	/**
	 * 表列名转属性名
	 * @param columnName
	 * @param underlineTurnCamel
	 * @return columnName
	 */
	public String columnNameTurnPropertyName(String columnName ,boolean underlineTurnCamel){
		String propertyName = columnName;
		if (underlineTurnCamel) { // 字段转属性
			propertyName = Underline2CamelUtils.underline2Camel2(columnName);
		}
		return propertyName;
	}

	/**
	 * 表列类型转属性类型
	 * @param columnType
	 * @return String propertyType
	 */
	public String columnTypeTurnPropertyType(String columnType){

		columnType = columnType.toLowerCase();
		if (columnType.equals("varchar") || columnType.equals("nvarchar") || columnType.equals("char")
				|| columnType.equals("text") || columnType.equals("mediumtext")) // ||
		{
			return "String";
		} else if (columnType.equals("tinyblob") || columnType.equals("blob") || columnType.equals("mediumblob")
				|| columnType.equals("longblob")) {
			return "byte[]";
		} else if (columnType.equals("datetime") || columnType.equals("date") || columnType.equals("timestamp")
				|| columnType.equals("time") || columnType.equals("year")) {
			return "Date";
		} else if (columnType.equals("bit") || columnType.equals("int") || columnType.equals("tinyint")
				|| columnType.equals("smallint") ||  columnType.equals("integer"))
		{
			return "Integer";
		} else if (columnType.equals("int unsigned")) {
			return "Integer";
		} else if (columnType.equals("bigint unsigned") || columnType.equals("bigint")) {
			return "Long";
		} else if (columnType.equals("float")) {
			return "Float";
		} else if (columnType.equals("double")) {
			return "Double";
		} else if (columnType.equals("decimal")) {
			return "BigDecimal";
		}
		return "ErrorType";
	}

	/**
	 *  获取表里所有信息包括转换后的属性名和属性类型
	 * @param metaData
	 * @param tableName
	 * @param underlineTurnCamel
	 * @return
	 * @throws SQLException
	 */
	public List<PropertyAndColumnInfo> getAllPropertysAndColumnInfos(DatabaseMetaData metaData, String tableName ,boolean underlineTurnCamel) throws SQLException {
		ResultSet colRet = metaData.getColumns(null, "%", tableName, "%");
		List<PropertyAndColumnInfo> propertysAndColumnInfos = new ArrayList<PropertyAndColumnInfo>();
		while (colRet.next()) {
			String columnName = colRet.getString("COLUMN_NAME"); //列名
			String columnType = colRet.getString("TYPE_NAME"); //列类型（VARCHAR、INTEGER等）
			String columnRemarks = colRet.getString("remarks");  //列的注释
			int isNullable = colRet.getInt("NULLABLE");  //是否可为空
			int columnSize = colRet.getInt("COLUMN_SIZE"); //列的大小
			int decimalDigits =  colRet.getInt("DECIMAL_DIGITS");//小数的位数

			if ("int".equals(columnType) || "INT".equals(columnType)){
				columnType = "INTEGER";
			}
			if ("text".equals(columnType) || "TEXT".equals(columnType)){
				columnType = "VARCHAR";
			}

			String propertyName=columnNameTurnPropertyName(columnName,underlineTurnCamel); // 通过表的列名获取属性名
			String propertyType=columnTypeTurnPropertyType(columnType); //通过表的列类型获取属性类型
			PropertyAndColumnInfo info = new PropertyAndColumnInfo();
			info.setColumnName(columnName);
			info.setColumnType(columnType);
			info.setColumnRemarks(columnRemarks);
			info.setIsNullable(isNullable);
			info.setColumnSize(columnSize);
			info.setDecimalDigits(decimalDigits);
			info.setPropertyName(propertyName);
			info.setPropertyType(propertyType);
			propertysAndColumnInfos.add(info);
			logging.info(
					 "columnRemarks:"+columnRemarks + ", columnName:" + columnName + ", columnType:" + columnType +
							 ", propertyName:"+propertyName + " propertyType:" +propertyType+
							 ", columnSize:" + columnSize + ", decimalDigits:" + decimalDigits + ", isNullable:" + isNullable
			);


		}
		return propertysAndColumnInfos;
	}

	/**
	 * 获取TableResultSet
	 *
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getTableResultSet(DatabaseMetaData metaData) throws SQLException {
		// DatabaseMetaData metaData = connection.getMetaData();
		// ResultSet tableRet = metaData.getTables(null, "%", "%", new String[]
		// { "TABLE" });
		String tableName = "%";
		return getTableResultSet(metaData, tableName);

	}

	/**
	 * 获取TableResultSet
	 *
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getTableResultSet(DatabaseMetaData metaData, String tableName) throws SQLException {
		ResultSet tableRet = metaData.getTables(null, "%", tableName, new String[] { "TABLE" });
		return tableRet;
	}

	/**
	 * 获取DatabaseMetaData
	 *
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	public DatabaseMetaData getMetaData(Connection connection) throws SQLException {
		DatabaseMetaData metaData = connection.getMetaData();
		return metaData;
	}

	/**
	 * 如果table名是t_开头，则去掉t_,其他_变驼峰，第一个字母大写。
	 *
	 * @param tableName
	 * @return
	 */
	public static String getClassName(String tableName, boolean underline2Camel) {
		String res = tableName;
		// 去t_
		if (res.startsWith("t_")) {
			res = res.substring(2);
		}

		if (underline2Camel) {
			// 变驼峰
			res = Underline2CamelUtils.underline2Camel2(res);
		}
		// 首字符大写
		res = res.substring(0, 1).toUpperCase() + res.substring(1);
		return res;
	}

	/**
	 * 设置字段类型 MySql数据类型
	 *
	 * @param columnType
	 *            列类型字符串

	 * @return
	 */
	public static String getFieldType(String columnType) {

		columnType = columnType.toLowerCase();
		if (columnType.equals("varchar") || columnType.equals("nvarchar") || columnType.equals("char")
				|| columnType.equals("text") || columnType.equals("mediumtext")) // ||
		// columnType.equals("tinytext")||columnType.equals("mediumtext")||columnType.equals("longtext")
		{
			return "String";
		} else if (columnType.equals("tinyblob") || columnType.equals("blob") || columnType.equals("mediumblob")
				|| columnType.equals("longblob")) {
			return "byte[]";
		} else if (columnType.equals("datetime") || columnType.equals("date") || columnType.equals("timestamp")
				|| columnType.equals("time") || columnType.equals("year")) {
//			packages.add("import java.util.Date;");
			return "Date";
		} else if (columnType.equals("bit") || columnType.equals("int") || columnType.equals("tinyint")
				|| columnType.equals("smallint")) // ||columnType.equals("bool")||columnType.equals("mediumint")
		{
			return "Integer";
		} else if (columnType.equals("int unsigned")) {
			return "Integer";
		} else if (columnType.equals("bigint unsigned") || columnType.equals(columnType.toLowerCase())) {
//			packages.add("import java.math.BigInteger;");
			return "Long";
		} else if (columnType.equals("float")) {
			return "Float";
		} else if (columnType.equals("double")) {
			return "Double";
		} else if (columnType.equals("decimal")) {
//			packages.add("import java.math.BigDecimal;");
			return "BigDecimal";
		}
		return "ErrorType";
	}

	/**
	 * 设置类标题注释
	 *
	 * @param packages
	 * @param className
	 */
	public static void getTitle(StringBuilder packages, String className) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
		packages.append("\r\n/**\r\n");
		packages.append("*\r\n");
		packages.append("* 标题: " + className + "<br/>\r\n");
		packages.append("* 说明: <br/>\r\n");
		packages.append("*\r\n");
		packages.append("* 作成信息: DATE: " + format.format(new Date()) + " NAME: author\r\n");
		packages.append("*\r\n");
		packages.append("* 修改信息<br/>\r\n");
		packages.append("* 修改日期 修改者 修改ID 修改内容<br/>\r\n");
		packages.append("*\r\n");
		packages.append("*/\r\n");
	}

}
